if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hrm_menu]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hrm_menu]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hrm_login_user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hrm_login_user]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hrm_menu_role]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hrm_menu_role]
GO

CREATE TABLE [dbo].[hrm_menu] (
	[menu_id] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[menu_name] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[parent_id] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[menu_url] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[use_flag] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[menu_desc] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[hrm_login_user] (
	[login_id] [bigint] IDENTITY (1, 1) NOT NULL ,
	[login_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[login_pwd] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[login_user_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[login_role] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[login_remark] [varchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
	[use_flag] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[create_date] [datetime] NULL ,
	[update_date] [datetime] NULL ,
	[create_by] [bigint] NULL ,
	[update_by] [char] (8) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[hrm_menu_role] (
	[menu_role_id] [bigint] IDENTITY (1, 1) NOT NULL ,
	[role_id] [varchar] (60) NOT NULL ,
	[menu_id] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

create table hrm_common{
 [common_id] [bigint] identity (1, 1) not null,
 [common_type] varchar(60) not null,
 [common_value] varchar(60),
 [common_code]  varchar(60)
}

create table hrm_area{
	[area_id] [bigint] identity (1, 1) not null,
	[area_code] varchar(60) not null,
	[area_value] varchar(60) not null
}

create table hrm_province{
	[province_id] [bigint] identity (1, 1) not null,
	[province_code] varchar(60) not null,
	[area_code] varchar(60) not null
}

create table hrm_city{
	[city_id] [bigint] identity (1, 1) not null,
	[city_level] varchar(60) not null,
	[city_code] varchar(60) not null,
	[city_value] varchar(60) not null,
	[province_code] varchar(60) not null,
	[citys_code] varchar(60)
}

create table hrm_citys{
	[citys_id] [bigint] identity (1, 1) not null,
	[citys_code] varchar(60) not null,
	[citys_value] varchar(60) not null
}


CREATE TABLE [dbo].[hrm_emp_info] (
	[emp_id] [bigint] NOT NULL ,
	[emp_area_value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_area_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_province_value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_province_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_city_value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_city_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_ctm_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[client_type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_name] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[aiwang] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_job] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_level] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_city_level] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_sex] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_height] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_birth_place] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_minzu] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_xueli] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_birthdate] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_phone_mobile] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_phone_native] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_mail] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_juzhudi] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_info_bakup] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_hetong_type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_idcard] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_hukou_type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_login_yn] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_login_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_login_pwd] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_role] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_yuangongsi_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_huayi_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_shengzhi_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_zhuanzheng_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_lizhi_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_lizhidan_yn] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_lizhi_yuanyin] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_hentong_end] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_canbao_city] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_canbao_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_canbao_type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_weicanbao_yuanyin] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_canbao_bakup] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_bank] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_bank_city] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_bank_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_luli_yn] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_luli_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_lizhizhengming_yn] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_lizhizhengming_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_idcopy_yn] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_idcopy_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_hukoucopy_yn] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_hukoucopy_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_hukoutypecopy_yn] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_hukoutypecopy_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_picture_yn] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_picture_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_yunjian_yn] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_yunjian_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_tijian_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_tijian_yn] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_rusi_yn] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_rusi_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_shouce_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_shouce_yn] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_buquan] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_ziliao_bakup] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[hrm_shop_info] (
	[shop_id] [bigint] identity (1, 1) NOT NULL,
	[shop_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	
	[shop_maichang_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_maichang_value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_maichang_type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	
	[shop_area_value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_area_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	
	[shop_province_value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_province_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	
	[shop_city_value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_city_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	
	[shop_citys_value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_citys_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	
	[shop_position_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_mail_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_position_value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	
	[shop_jingxiaoshang_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_jingxiaoshang_value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_jingxiaoshang_type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	
	[shop_level]  [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	
	[shop_ctm_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_ctm_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[del_flag] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
	
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hrm_area_rtm]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hrm_area_rtm]
GO

CREATE TABLE [dbo].[hrm_area_rtm] (
	[area_rtm_id] [bigint] IDENTITY (1, 1) NOT NULL ,
	[area_code] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,
	[area_value] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_id] [bigint] NULL ,
	[emp_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[emp_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[hrm_shop_score] (
	[shop_score_id] [bigint] IDENTITY (1, 1) NOT NULL ,
	[start_time] [VARCHAR] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[end_time] [VARCHAR] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_id] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,
	[tufashijian_code] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,
	[tufashijian_value] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL,
	[total_score] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hrm_baoxiao_detail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hrm_baoxiao_detail]
GO

CREATE TABLE [dbo].[hrm_baoxiao_detail] (
	[baoxiao_detail_id] [bigint] IDENTITY (1, 1) NOT NULL ,
	[baoxiao_time] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[baoxiao_lastmodify_time] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[baoxiao_index] [int] NULL ,
	[ctm_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[ctm_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[city_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[city_value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[rtm_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[rtm_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[fee_largeclass] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[fee_smallclass] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[fee_type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[fee] [float] NULL ,
	[fee_starttime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[fee_endtime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[del_flag] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hrm_baoxiao_shenpi]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hrm_baoxiao_shenpi]
GO

CREATE TABLE [dbo].[hrm_baoxiao_shenpi] (
	[baoxiao_shenpi_id] [bigint] IDENTITY (1, 1) NOT NULL ,
	
	[baoxiao_create_time] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
	
	[baoxiao_lastmodify_time] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
	
	[baoxiao_rtmshenpi_time] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
	
	[baoxiao_zongbushenpi_time] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
	
	[baoxiao_create_index] [int]  NULL,
	
	[ctm_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
	[ctm_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	
	[city_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
	[city_value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
	
	[rtm_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
	[rtm_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
	
	[ctm_mail] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
	
	[rtm_mail] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
	
	[zongbu_mail] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
	
	[rtm_pifu_yijian] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL,
	[zongbu_pifu_yijian] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL,
	
	[baoxiao_state_code] [int] NULL ,
	[baoxiao_rtm_statecode] [int] NULL ,
	[baoxiao_zongbu_statecode] [int] NULL ,
	
	[tongxun_fee] [float]  NULL,
	[traffic_fee] [float]  NULL,
	[zhusu_fee] [float]  NULL,
	[canbu_fee] [float]  NULL,
	[chailv_fee] [float]  NULL,
	
	[za_fee] [float]  NULL,
	[kuaidi_fee] [float]  NULL,
	[computer_fee] [float]  NULL,
	[lastmouth_fee] [float]  NULL,
	
	[total_fee] [float]  NULL,
	
	[del_flag] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL 
)ON [PRIMARY]
GO




